Purpose

We will be creating a financial performance report for CheeseCo (a hypothetical company which produces cheese) using their annual income statements and balance sheets. This is to demonstrate the capabilities of a Python package I have built from scratch: finModel (for more details about the package please go through the README.md in the root directory)

Summary highlights:

• The company's profits (EBITDA) ranged between 11-14% of revenues on an average and based on historical averages, the share is expected to decline (see fig. 1)
• We expected the unlevered free cashflows to fall from 10 mil. USD in FY2019 to 7 mil. at FY2020, followed by a consistent rise till 9 mil. over the next 3 years (see fig. 2)
• On an average, the COGs (propelled by it's direct costs) is nearly 80% of it's revenues, which explains the 20% EBITDA we saw earlier
• 2018 seems to be the most profitable year with 8 mil. USD more profit than the previous year, and forecasted a fall by 2.5 mil in 2019 driven by increased costs during the year (see fig. 3)
• On an average, it takes about 70 days for the company to collect the revenue after sale. On the other hand, it can do better in clearing it's inventories as it took 79 days in 2016 which rose to 106 days by 2018. This has impacted the payments to suppliers from 40 days in 2016 to 83 (2x) by 2018 (see fig. 4)
• The company seems to make sales from both fresh stock and inventories equivalently, this might have to do with the need to cater to seasonal demand for the product
• DCF analysis tells us that the company's Enterprise value as on 2016 stands at 105 mil. out which 30% will be coming from cashflows and equity was 62 mil, assuming 10% weighted-average cost-of-capital and 2% long-term growth rate (see DCF summary table in section 1.4)
• Simulations reveal that the enterprise value can be 135 mil at 9% WACC, 3 % long-term growth and 25% being PV of future cashflows (see scenario 3 of fig. 6 and 7)

User parameters:

root_dir = 'C:/Users/ssmal/learning/Projects/py-packages/financial_model'
file_loc = f'{root_dir}/data/cheesco-source-data-2016-2018.xlsx'
actual_dates = ['2016-12-31','2017-12-31','2018-12-31']
forecast_dates = ['2019-12-31','2020-12-31','2021-12-31','2022-12-31','2023-12-31']

Python set-up

from finModel.viz.plots import plot_ebitda_revenue_ratio, plot_unlevered_cashflows, plot_ebitda_component
from finModel.viz.plots import plot_days_component, plot_wc_component, plot_enterprise_vals
from finModel.statements.main import FinancialStatement
from finModel.analysis.dcf_valuation import DCFValuation
from extract import source_balance, source_income
from finModel.analysis.ratios import FinRatio
from finModel.viz.generic import ChartLabels, style_statement, style_table
from typing import List
import plotly.graph_objects as go
import pandas as pd
import numpy as np

%load_ext autoreload
%aimport finModel.analysis.ratios, finModel.viz.plots, finModel.viz.generic
%autoreload 1

statement = FinancialStatement(comp="Cheese Co.",
                               a_date=actual_dates,
                               f_date=forecast_dates,
                               inc=source_income(file_loc,actual_dates),
                               bs=source_balance(file_loc,actual_dates))

dcf = DCFValuation(wacc=0.1,lt_growth=0.02,fin=statement)

chart_data = FinRatio(dcf)

charts: List[go.Figure] = [
    plot_ebitda_revenue_ratio(chart_ttl="Figure 1: EBITDA (% of revenue) and Revenue from sales and services",
                              data=chart_data.ebitda_rev_ratio),
    plot_unlevered_cashflows(chart_ttl="Figure 2: Unlevered free cash flows evolution across forecast periods",
                             data=chart_data.ufcf_trend),
    plot_ebitda_component(chart_ttl="Figure 3: Breaking up EBITDA into it's major components",
                          data=chart_data.ebitda_components),
    plot_days_component(chart_ttl="Figure 4: Working capital development (days outstanding)",
                        data=chart_data.working_capital),
    plot_wc_component(chart_ttl="Figure 5: Working capital development (value accumulated)",
                      data=chart_data.working_capital),
    plot_enterprise_vals(lbl=ChartLabels(x="WACC",xlab="",y="Enterprise Value",ylab="USD in thousands",y2="Long-term growth",
                                         ttl="Figure 6: Simulating enterprise values by various WACC and g"),
                         data=dcf.simulate_enterprise_val()),
    plot_enterprise_vals(lbl=ChartLabels(x="CFs in Forecast period",xlab="",y="Enterprise Value",ylab="USD in thousands",
                                         ttl="Figure 7: Simulating enterprise values by Continuing values and Forecasted CFs",
                                         y2="Continuing Value"),
                         data=dcf.simulate_enterprise_val()),
]

1. Financial statements

1.1: Income statement

style_statement(data=statement.income.to_pandas_df(),
                f_date=statement.forecast_dates,
                bold_rows=["Total revenues","Gross margin","EBITDA","EBIT","EBT","Net income"],
                pct_rows=["Tax rate"])

component	2016A	2017A	2018A	2019F	2020F	2021F	2022F	2023F
Revenues	177,203	188,618	208,366	225,985	245,094	265,819	288,296	312,673
Other revenues	3,520	2,416	2,585	2,585	2,585	2,585	2,585	2,585
Total revenues	180,723	191,034	210,951	228,570	247,679	268,404	290,881	315,258
Cost of goods sold	(142,394)	(150,572)	(157,523)	(177,613)	(192,632)	(208,920)	(226,586)	(245,746)
Gross margin	38,329	40,462	53,428	50,957	55,047	59,483	64,295	69,513
Operating expenses	(18,011)	(19,248)	(23,867)	(23,972)	(25,999)	(28,197)	(30,582)	(33,168)
EBITDA	20,317	21,214	29,561	26,985	29,048	31,286	33,713	36,345
D&A	(2,908)	(4,650)	(6,431)	(5,418)	(5,876)	(6,373)	(6,912)	(7,496)
EBIT	17,410	16,564	23,130	21,567	23,172	24,913	26,801	28,849
Interest expense	(1,266)	(2,191)	(2,664)	(2,664)	(2,664)	(2,664)	(2,664)	(2,664)
Extraordinary income	(318)	(864)	(320)
EBT	15,826	13,509	20,146	18,903	20,508	22,249	24,137	26,185
Tax rate	(24.05%)	(22.58%)	(18.94%)	(21.85%)	(21.85%)	(21.85%)	(21.85%)	(21.85%)
Taxes	(3,806)	(3,050)	(3,815)	(4,131)	(4,482)	(4,862)	(5,275)	(5,723)
Net income	12,020	10,459	16,331	14,772	16,026	17,387	18,862	20,462

1.2: Balance Sheet

style_statement(data=statement.balance.to_pandas_df(),
                f_date=statement.forecast_dates,
                bold_rows=["Total Assets","Total Liabilities & Equities"])

component	2016A	2017A	2018A	2019F	2020F	2021F	2022F	2023F
Intangible assets	5,962	5,840	5,650	5,650	5,650	5,650	5,650	5,650
PP&E	45,106	49,072	56,109	59,057	64,051	69,467	75,341	81,712
Financial assets	8,824	9,831	10,323	10,323	10,323	10,323	10,323	10,323
Inventory	31,167	36,397	46,212	44,638	48,413	52,506	56,946	61,762
Trade receivable	32,884	38,556	43,582	45,132	48,949	53,088	57,577	62,445
Other assets	8,842	11,359	17,603	14,659	15,898	17,243	18,701	20,282
Cash and equivalents	11,791	19,409	8,174	16,789	22,246	28,169	34,599	41,577
Total Assets	144,576	170,464	187,652	196,249	215,529	236,446	259,136	283,750
Trade payable	15,891	26,352	36,190	30,570	33,155	35,959	38,999	42,297
Other liabilities	5,969	5,800	8,473	7,917	8,587	9,313	10,100	10,954
Financial liabilities	41,293	50,645	51,121	51,121	51,121	51,121	51,121	51,121
Shareholder's equity	81,422	87,667	91,868	106,640	122,667	140,054	158,916	179,378
Total Liabilities & Equities	144,576	170,464	187,652	196,249	215,529	236,446	259,136	283,750

1.3: Cashflow statement

style_statement(data=statement.cash.to_pandas_df(),
                f_date=statement.forecast_dates,
                bold_rows=["NOPAT","Gross Cash Flow","Investments in working capital","Investments in other assets/liabilities",
                           "Capex","Other investments","Extraordinary items","UFCF","Net Cash Flow"],
                pct_rows=["Operating tax rate"])

component	2016A	2017A	2018A	2019F	2020F	2021F	2022F	2023F
EBIT	17,410	16,564	23,130	21,567	23,172	24,913	26,801	28,849
Operating taxes	(4,187)	(3,740)	(4,380)	(4,713)	(5,064)	(5,445)	(5,857)	(6,305)
Operating tax rate	(24.05%)	(22.58%)	(18.94%)	(21.85%)	(21.85%)	(21.85%)	(21.85%)	(21.85%)
NOPAT	13,223	12,824	18,750	16,854	18,108	19,469	20,944	22,544
Add-back D&A	2,908	4,650	6,431	5,418	5,876	6,373	6,912	7,496
Gross Cash Flow	16,131	17,474	25,181	22,272	23,984	25,841	27,856	30,040
Inventory (movement)		(5,230)	(9,815)	1,574	(3,775)	(4,094)	(4,440)	(4,815)
Trade receivables (movement)		(5,672)	(5,026)	(1,551)	(3,816)	(4,139)	(4,489)	(4,869)
Trade payable (movement)		10,460	9,838	(5,619)	2,585	2,804	3,041	3,298
Net change in working capital		(442)	(5,003)	(5,596)	(5,006)	(5,429)	(5,888)	(6,386)
Net change in other asset/liabilities		(2,686)	(3,571)	2,388	(570)	(618)	(671)	(727)
Capex (movement)		(8,616)	(13,467)	(8,366)	(10,870)	(11,789)	(12,786)	(13,867)
Other investment (movement)		(885)	(302)
Extra-ordinary items	(318)	(864)	(320)
Unlevered Free Cashflows		3,981	2,518	10,697	7,538	8,005	8,511	9,060
Interest expenses	(1,266)	(2,191)	(2,664)	(2,664)	(2,664)	(2,664)	(2,664)	(2,664)
Delta Taxes vs. Operating taxes	381	690	565	582	582	582	582	582
Delta Financial liabilities		9,352	475
Delta Equity (inc. dividends)		(4,214)	(12,130)	0	(0)	0	(0)
Net Cashflow		7,618	(11,235)	8,615	5,457	5,923	6,429	6,978

1.4: DCF valuation

style_table(df=dcf.to_pandas_df(),
            bold_rows=["PV of Cash flows","PV of Continuing value","Enterprise value","Equity value"],
            pct_rows=["WACC","long-term growth","PV of Cash flows (%)","PV of Continuing value (%)"])

metric
WACC	10.00%
long-term growth	2.00%
PV of Cash flows	33,408
PV of Cash flows (%)	31.78%
Continuing value	115,515
PV of Continuing value	71,726
PV of Continuing value (%)	68.22%
Enterprise value	105,134
(-) Financial liabilities	(51,121)
(+) Cash	8,174
Equity value	62,187

2. Visualisation

• Hover your mouse on the graph to see the exact nos. and related details
• You can hide a series from being plotted by left-clicking on it's legend
• Click on the "camera" button in the chart panel to download as an image file

[c.show() for c in charts]

[None, None, None, None, None, None, None]